﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TeacherHelp.DAL
{
    public class TestService
    {
        public string GetTestResultAllSQL()
        {
            string sql = @"
                        select a.Id,
								c.[Name],
                                c.[LoginName],
								b.[Name] SubjectName,
								a.StartTime,
								a.EndTime,
								a.Score,
								a.UserId,
								a.SubjectsId SubjectId 
								from Test a
						left join Subjects b on a.SubjectsId = b.Id
						left join [User] c on a.UserId = c.Id
						where 1 = 1 ";
            return sql;
        }

        public string GetTestSQL()
        {
            string sql = @"
            SELECT [Id]
			  ,[StartTime]
			  ,[EndTime]
			  ,[SubjectsId]
			  ,[ExercisesList]
			  ,[UserId]
			  ,[Score]
			FROM[TeacherHelpDB].[dbo].[Test]";
            return sql;
        }

        public string GetWhereIdSQL(string Id)
        {
            string sql = "WHERE ID = " + Id;
            return sql;
        }

        public string GetWhereSubjectNameSQL(string subjectName)
        {
            string sql = $" and b.[Name] = '{subjectName}'";
            return sql;
        }

        public string GetWhereDateSQL(string date)
        {
            string sql = $" and DATEDIFF(day,a.StartTime,'{date}')=0";
            return sql;
        }

        public string GetWhereUserIdSQL(string UserId)
        {
            string sql = $" and a.UserId in ({UserId}) ";
            return sql;
        }

    }
}
